package com.xnx3.wangmarket.admin.service.impl;

import com.aliyun.common.utils.DateUtil;
import com.xnx3.exception.NotReturnValueException;
import com.xnx3.j2ee.dao.SqlDAO;
import com.xnx3.j2ee.entity.User;
import com.xnx3.j2ee.func.Sql;
import com.xnx3.j2ee.service.SqlService;
import com.xnx3.j2ee.vo.BaseVO;
import com.xnx3.wangmarket.Utils.DateFormatUtils;
import com.xnx3.wangmarket.Utils.SystemUtils;
import com.xnx3.wangmarket.admin.entity.*;
import com.xnx3.wangmarket.admin.service.CompanyInfoService;
import com.xnx3.wangmarket.admin.vo.CompanyInfoVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.management.Query;
import javax.servlet.http.HttpServletRequest;
import java.util.*;

import static com.xnx3.j2ee.shiro.ShiroFunc.getUserId;

@Service("companyInfoService")
public class CompanyInfoImpl implements CompanyInfoService {
    @Resource
    private SqlDAO sqlDAO;

    @Resource
    private SqlService sqlService;

    @Override
    public List<CompanyInfo> queryCompanyInfo(int userid) {
        return sqlDAO.findBySqlQuery("select * from Company_Info where user_id=" + userid, CompanyInfo.class);
    }

    @Override
    public void updateCompanyInfo(CompanyInfo companyInfo, int userid, HttpServletRequest request) {
        sqlDAO.save(companyInfo);
    }

    @Override
    public void delCompanyInfo(CompanyInfo companyInfo) {
        sqlDAO.delete(companyInfo);
    }

    @Override
    public List<Product> queryProductInfo(int userid) {
        return sqlDAO.findBySqlQuery("select * from product where user_id=" + userid + "order by timestamps desc limit 1", Product.class);
    }

    @Override
    public List<News> queryNewList(int userid, Integer id) {
        return sqlDAO.findBySqlQuery("SELECT * FROM news where userid = " + userid + " and cid=" + id, News.class);
    }

    @Override
    public List<Product> queryProduct(int userid) {
        return sqlDAO.findBySqlQuery("SELECT  *  FROM   product  where user_id =" + userid + " ORDER BY  TIMESTAMPs  desc  LIMIT   4", Product.class);
    }

    @Override
    public List<News> queryNews(int userid, int cid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `news` where userid = " + userid + " and cid=" + cid + " and type=1 ORDER BY addtime DESC LIMIT 5", News.class);
    }

    @Override
    public List<News> queryAllNews(int userid, int cid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `news` where userid = " + userid + " and cid = " + cid + " and type=1 ORDER BY addtime DESC LIMIT 5", News.class);
    }

    @Override
    public List<NewsData> queryNewsData(int id) {
        return sqlDAO.findBySqlQuery("select * from news_data where id =" + id, NewsData.class);
    }

    @Override
    public List<SiteColumn> queryNewsOneColumn(int userid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `site_column` where userid=" + userid + " and type=1", SiteColumn.class);
    }

    @Override
    public List<SiteColumn> queryNewsTwoColumn(int parentid, int userid) {
        return sqlDAO.findBySqlQuery("select * from site_column where parentid = " + parentid + " and userid = " + userid, SiteColumn.class);
    }

    @Override
    public List<EmpiricalConclusion> queryEmpirical(int userid) {
        return sqlDAO.findBySqlQuery("SELECT  *  FROM   empirical_conclusion  where user_id =" + userid + " ORDER BY  TIMESTAMPs  desc LIMIT   4", EmpiricalConclusion.class);
    }

    @Override
    public List<Product> twoSelProduct(int siteColumnId, int userid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `product` where site_column_id = " + siteColumnId + " and user_id = " + userid, Product.class);
    }

    @Override
    public List<EmpiricalConclusion> twoSelEmpircal(int siteColumnId, int userid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `empirical_conclusion` where site_column_id = " + siteColumnId + " and user_id = " + userid, EmpiricalConclusion.class);
    }

    @Override
    public List<Product> allProduct() {
        return sqlDAO.findBySqlQuery("SELECT  *  FROM   product  ORDER BY  TIMESTAMPs  desc  LIMIT   4", Product.class);
    }

    @Override
    public List<Site> querySite(int userId) {
        return sqlDAO.findBySqlQuery("select * from site where userId = " + userId, Site.class);
    }

    @Override
    public List<Site> queryCountNum() {
        List<Site> site = sqlDAO.findBySqlQuery("select a.*,c.title,c.titlepic,c.intro from site a \n" +
                "LEFT JOIN site_column b on a.id = b.siteid and b.type='3' and b.`name`  in('公司简介','基本信息') \n" +
                "INNER JOIN news c on a.id = c.siteid and b.id = c.cid ORDER BY a.count_Num desc", Site.class);
        List<News> news = sqlDAO.findBySqlQuery("select c.* from site a \n" +
                "LEFT JOIN site_column b on a.id = b.siteid and b.type='3' and b.`name`  in('公司简介','基本信息') \n" +
                "INNER JOIN news c on a.id = c.siteid and b.id = c.cid ORDER BY a.count_Num desc", News.class);
        List<User> users = sqlDAO.findBySqlQuery("select * from user where id in(\n" +
                "select c.userid from site a \n" +
                "LEFT JOIN site_column b on a.id = b.siteid and b.type='3' and b.`name`  in('公司简介','基本信息') \n" +
                "INNER JOIN news c on a.id = c.siteid and b.id = c.cid ORDER BY a.count_Num)\n", User.class);

        List<Site> list = new ArrayList<>();
        for (Site st : site) {
            for (News ns : news) {
                if (st.getId().equals(ns.getSiteid())) {
                    st.setIntro(ns.getIntro());
                    st.setTitlepic(ns.getTitlepic());
                    st.setTitle(ns.getTitle());
                    for (User user : users) {
                        if (user.getId().equals(st.getUserid())) {
                            st.setRoleCode(user.getRoleCode());
                            if (user.getRoleCode() == null || user.getRoleCode().equals("QYYH")) {
                                st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("ZMXC")) {
                                st.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("EXPERT")) {
                                st.setUrl("http://118.25.235.213:8030/zj/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("HZSYH")) {
                                st.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("ZZDH")) {
                                st.setUrl("http://118.25.235.213:8030/zydh/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("GXSYH")) {
                                st.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + user.getId());
                            }else if (user.getRoleCode().equals("JDYH")) {
                                st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());

                            }
                        }
                    }
                    list.add(st);
                }
            }
        }

        Collections.sort(list);
        return list;
    }

    @Override
    public void updateCountNum(int userId, int count) {
        sqlDAO.executeSql("update site  set count_Num=" + count + " where userId=" + userId);
    }

    @Override
    public List<Site> queryNum(int userId) {
        return sqlDAO.findBySqlQuery("select * from site where userid=" + userId, Site.class);
    }

    @Override
    public List<News> queryNews(int page, int count, News news) {
        return sqlDAO.findBySqlQuery("select t.* from news t join (\n" +
                "select b.id id from user a join site b on a.id = b.userid where a.role_code='ZMXC') w on t.siteid = w.id\n" +
                "GROUP BY t.userid limit " + ((page - 1) * 10) + "," + count, News.class);
    }

    @Override
    public Page findNewsPage(int page, int count, News news) {

        List<News> pctList = queryNews(page, count, news);
        int totle = pctList.size();
        Page p = new Page();
        p.setNewsList(pctList);
        p.setCurrentPage(page);
        p.setCount(count);
        p.setTotalCount(totle);
        int totlePage = totle % count == 0 ? totle / count : (totle / count) + 1;
        p.setTotalPage(totlePage);

        return p;
    }

    @Override
    public Map<String, Object> getServiceDemon(String areaCode,int num) {
        List<VUserIntroView> VUserIntroViews = sqlDAO.findBySqlQuery(" select a.userid,a.role_code,a.area_Code,a.id,a.titlepic,\n" +
                "a.intro,a.company_Name,a.count_Num  from v_user_intro_view a left join v_user_intro_view b\n" +
                "on a.role_code=b.role_code and a.id>=b.id\n" +
                "where a.area_code like '"+areaCode+"%' and  A.role_code is not null\n" +
                "group by a.role_code,a.id\n" +
                "having count(b.id)<= "+num+" order by role_code, a.count_Num desc;", VUserIntroView.class);
        Map<String, Object> map = new HashMap<>();
        List<VUserIntroView> list = new ArrayList<>();
        List<VUserIntroView> list1 = new ArrayList<>();
        List<VUserIntroView> list2 = new ArrayList<>();
        List<VUserIntroView> list3 = new ArrayList<>();
        List<VUserIntroView> list4 = new ArrayList<>();
        List<VUserIntroView> list5 = new ArrayList<>();
        List<VUserIntroView> list6= new ArrayList<>();
        for (VUserIntroView vs : VUserIntroViews) {
            if (vs.getRoleCode().equals("QYYH")) {
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getUserid());
                list.add(vs);
            }else if(vs.getRoleCode().equals("ZMXC")){
                vs.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + vs.getUserid());
                list1.add(vs);
            }else if(vs.getRoleCode().equals("EXPERT")){
                vs.setUrl("http://118.25.235.213:8030/zj/#/mains/" + vs.getUserid());
                list2.add(vs);
            }else if(vs.getRoleCode().equals("HZSYH")){
                vs.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + vs.getUserid());
                list3.add(vs);
            }else if(vs.getRoleCode().equals("ZZDH")){
                vs.setUrl("http://118.25.235.213:8030/zydh/#/mains/" + vs.getUserid());
                list4.add(vs);
            }else if(vs.getRoleCode().equals("GXSYH")){
                vs.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + vs.getUserid());
                list5.add(vs);
            }else if(vs.getRoleCode().equals("JDYH")){
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getUserid());
                list6.add(vs);
            }
        }
        map.put("企业", list);
        map.put("最美乡村", list1);
        map.put("专家", list2);
        map.put("合作社", list3);
        map.put("种植大户", list4);
        map.put("供销社", list5);
        map.put("基地", list6);
        return map;
    }

    @Override
    public List<VUserIntroView> getPageServiceDemonstration(VUserIntroView VUserIntroView, int page, int num) {

        List<VUserIntroView> VUserIntroViews = sqlDAO.findBySqlQuery("select * from v_user_intro_view where area_code like '%" + VUserIntroView.getAreaCode() + "%' and role_code='"+VUserIntroView.getRoleCode()+"' limit "+((page - 1) * 10)+","+num,VUserIntroView.class);
       List<VUserIntroView> list = new ArrayList<>();

        for (VUserIntroView vs : VUserIntroViews) {
            if (vs.getRoleCode().equals("QYYH")) {
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getUserid());
                list.add(vs);
            }else if(vs.getRoleCode().equals("ZMXC")){
                vs.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + vs.getUserid());
                list.add(vs);
            }else if(vs.getRoleCode().equals("EXPERT")){
                vs.setUrl("http://118.25.235.213:8030/zj/#/mains/" + vs.getUserid());
                list.add(vs);
            }else if(vs.getRoleCode().equals("HZSYH")){
                vs.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + vs.getUserid());
                list.add(vs);
            }else if(vs.getRoleCode().equals("ZZDH")){
                vs.setUrl("http://118.25.235.213:8030/zydh/#/mains/" + vs.getUserid());
                list.add(vs);
            }else if(vs.getRoleCode().equals("GXSYH")){
                vs.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + vs.getUserid());
                list.add(vs);
            }else if(vs.getRoleCode().equals("JDYH")){
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getUserid());
                list.add(vs);
            }
        }
        return list;
    }

    @Override
    public Page findSitePage(VUserIntroView site, int page, int num) {

        List<VUserIntroView> pctList = getPageServiceDemonstration(site, page, num);

        int totle = getNumPage(site);
//        int totle = pctList.size();
        Page p = new Page();
        p.setVUserIntroViewList(pctList);
        p.setCurrentPage(page);
        p.setCount(totle);
        p.setTotalCount(totle);
        int totlePage = totle % totle == 0 ? totle / totle : (totle / totle) + 1;
        p.setTotalPage(totlePage);

        return p;
    }

    @Override
    public int getNumPage(VUserIntroView VUserIntroView) {
        List<VUserIntroView> VUserIntroViews = sqlDAO.findBySqlQuery("select * from v_user_intro_view where area_code like '%" + VUserIntroView.getAreaCode() + "%' and role_code='"+VUserIntroView.getRoleCode()+"'",VUserIntroView.class);
        return VUserIntroViews.size();
    }

    @Override
    public List<VUserNewsView> getLatestNews(int areaCode) {
        List<VUserNewsView> VUserIntroViews =  sqlDAO.findBySqlQuery("select * from real_time_user_news where area_code like '"+areaCode+"%' ORDER BY addtime desc LIMIT   5",VUserNewsView.class);

        List<VUserNewsView> list = new ArrayList<>();

        for (VUserNewsView vs : VUserIntroViews) {

                vs.setTime(DateFormatUtils.timeStamp2Date(vs.getAddtime(),"yyyy-MM-dd"));
            if (vs.getRoleCode().equals("QYYH")) {
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("ZMXC")){
                vs.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("EXPERT")){
                vs.setUrl("http://118.25.235.213:8030/zj/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("HZSYH")){
                vs.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("ZZDH")){
                vs.setUrl("http://118.25.235.213:8030/zydh/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("GXSYH")){
                vs.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("JDYH")){
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getId());
                list.add(vs);
            }
        }
        return list;
    }

    @Override
    public List<VUserNewsView> getPageNews(int areaCode, int page, int num) {

        StringBuffer sql = new StringBuffer();
        sql.append("select * from real_time_user_news where 1=1 ");
        if(areaCode>0&&!"".equals(areaCode)){
            sql.append(" and area_code like '"+areaCode+"%' ");
        }
        sql.append(" limit "+((page - 1) * 10)+","+num);
        List<VUserNewsView> VUserIntroViews = sqlDAO.findBySqlQuery(sql.toString(),VUserNewsView.class);

        List<VUserNewsView> list = new ArrayList<>();

        for (VUserNewsView vs : VUserIntroViews) {
            vs.setTime(DateFormatUtils.timeStamp2Date(vs.getAddtime(),"yyyy-MM-dd"));
            if (vs.getRoleCode().equals("QYYH")) {
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("ZMXC")){
                vs.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("EXPERT")){
                vs.setUrl("http://118.25.235.213:8030/zj/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("HZSYH")){
                vs.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("ZZDH")){
                vs.setUrl("http://118.25.235.213:8030/zydh/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("GXSYH")){
                vs.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + vs.getId());
                list.add(vs);
            }else if(vs.getRoleCode().equals("JDYH")){
                vs.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + vs.getId());
                list.add(vs);
            }
        }
        return list;
    }

    @Override
    public Page findNewsPage(int areaCode, int page, int num) {
        List<VUserNewsView> pctList = getPageNews(areaCode, page, num);

//        int totle = getNumPage(vUserNewsView);
        int totle = pctList.size();
        Page p = new Page();
        p.setvUserNewsViewList(pctList);
        p.setCurrentPage(page);
        p.setCount(totle);
        p.setTotalCount(totle);
        int totlePage = totle % totle == 0 ? totle / totle : (totle / totle) + 1;
        p.setTotalPage(totlePage);

        return p;
    }

    @Override
    public List<PersionQRcode> getQRcodes(int userid) {

        return sqlDAO.findBySqlQuery(" select * from persion_qrcode where userid='"+userid+"'",PersionQRcode.class);
    }

    @Override
    public List<PersionVideo> getVideos(int userid) {
        return sqlDAO.findBySqlQuery(" select * from persion_video where userid='"+userid+"'",PersionVideo.class);
    }

}
